package org.example.utils;

import org.example.entity.UserInfo;
import org.example.factory.mysql.UserInfoFactory;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;

/**
 * @author huangy
 * @since 2022/11/13 9:04 上午
 */
public class JDBCUtils {

    //数据库连接路径
    private static final String url = "jdbc:mysql://localhost:3306/chess_game?useServerPrepStmts=false&rewriteBatchedStatements=true&serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static final String user = "root";
    private static final String password = "123456";


    @Test
    public void addBatch() {
        //定义连接、statement对象
        Connection conn = null;
        PreparedStatement pstm = null;
        try {
            //加载jdbc驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接mysql
            conn = DriverManager.getConnection(url, user, password);
            //将自动提交关闭
            conn.setAutoCommit(false);
            //编写sql
            String sql = "INSERT INTO user_info VALUES (?,?,?,?,?)";
            //预编译sql
            pstm = conn.prepareStatement(sql);
            //开始总计时
            long bTime1 = System.currentTimeMillis();
            //循环100次，每次十万数据，一共1000万
            //开始循环
            for (int j = 0; j < 10000; j++) {
                //开启分段计时，计10W数据耗时
                long bTime = System.currentTimeMillis();
                List<UserInfo> userInfos = UserInfoFactory.createUserInfo(10000);
                for (UserInfo userInfo : userInfos) {
                    pstm.setString(1, String.valueOf(userInfo.getId()));
                    pstm.setString(2, userInfo.getAccount());
                    pstm.setString(3, userInfo.getProps());
                    pstm.setString(4, userInfo.getItems());
                    pstm.setString(5, userInfo.getStatistic());
                    pstm.addBatch();
                }
                //执行批处理
                pstm.executeBatch();
                //提交事务
                conn.commit();
                //关闭分段计时
                long eTime = System.currentTimeMillis();
                //输出
                System.out.println("成功插入1W条数据耗时：" + (eTime - bTime));
            }
            //关闭总计时
            long eTime1 = System.currentTimeMillis();
            //输出
            System.out.println("插入10000W数据共耗时：" + (eTime1 - bTime1));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}


